package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRDepartment;
import org.eevolution.model.X_HR_Employee;

import extend.org.compiere.utils.Util;

public class MM_Form_MB6 extends SvrProcess {
	//Form_MB6_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_C_BPartner_ID, p_MM_WorkWear_ID;
	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("C_BPartner_ID")) 
				p_C_BPartner_ID  = para[i].getParameterAsInt();
			else if (name.equals("MM_WorkWear_ID"))
				p_MM_WorkWear_ID = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "Form_MB6_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		sheet.getRow(9).getCell(0).setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName());
		MBPartner bPartner = new MBPartner(m_ctx, p_C_BPartner_ID, get_TrxName());
		sheet.getRow(15).getCell(5).setCellValue(bPartner.getLastName() == null ? " ": bPartner.getLastName());
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_HR_Employee employee = null;
		String sql =" SELECT HR_Employee.HR_Employee_ID FROM HR_Employee HR_Employee  WHERE HR_Employee.IsActive='Y' " +
				    " AND HR_Employee.AD_Client_ID = "+Env.getAD_Client_ID(m_ctx)+ " AND HR_Employee.AD_Org_ID = " + Env.getAD_Org_ID(m_ctx) + 
				    " AND HR_Employee.C_BPartner_ID = "+p_C_BPartner_ID;
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int p_HR_Employee_ID = 0;
			while(rs.next())
			{
				p_HR_Employee_ID = rs.getInt(1);
				employee = new X_HR_Employee(m_ctx, p_HR_Employee_ID, get_TrxName());
			}
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		sheet.getRow(15).getCell(30).setCellValue(employee.gethrm_sex().equals("M")? "Мужской": employee.gethrm_sex().equals("W") ? "Женский":" "); 
		sheet.getRow(16).getCell(3).setCellValue(bPartner.getFirstName()==null? "":bPartner.getFirstName());
		
		sql=" SELECT MAX(MM_WorkWear.Height) AS Height, MAX(MM_WorkWear.Clothing_Size) AS Clothing_Size, " +
			" MAX(MM_WorkWear.Shoe_Size) AS Shoe_Size, MAX(MM_WorkWear.HeadDress_Size) AS HeadDress_Size " +
			" FROM MM_WorkWear MM_WorkWear WHERE MM_WorkWear.C_BPartner_ID = " +p_C_BPartner_ID+
			" AND MM_WorkWear.IsActive='Y' AND MM_WorkWear.AD_Client_ID = " +Env.getAD_Client_ID(m_ctx)+
			" AND MM_WorkWear.AD_Org_ID = "+Env.getAD_Org_ID(m_ctx);
		
		int Height = 0, Clothing_Size = 0, Shoe_Size = 0, HeadDress_Size = 0;
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				Height = rs.getInt("Height");
				Clothing_Size = rs.getInt("Clothing_Size");
				Shoe_Size = rs.getInt("Shoe_Size");
				HeadDress_Size = rs.getInt("HeadDress_Size");
			}	
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		sheet.getRow(16).getCell(30).setCellValue(Height+" см");
		sheet.getRow(17).getCell(5).setCellValue(bPartner.getMiddleName() == null ? " " :bPartner.getMiddleName());
		sheet.getRow(17).getCell(34).setCellValue(String.valueOf(Clothing_Size));
		sheet.getRow(18).getCell(8).setCellValue(String.valueOf(bPartner.gethrm_tableNumber()));
		sheet.getRow(18).getCell(34).setCellValue(String.valueOf(Shoe_Size));
		sheet.getRow(19).getCell(7).setCellValue((new MHRDepartment(m_ctx, employee.getHR_Department_ID(), get_TrxName())).getName());
		sheet.getRow(19).getCell(38).setCellValue(String.valueOf(HeadDress_Size));
		
		sql = " SELECT HRM_MainSpeciality,StartDate,HRM_Position FROM HRM_ProfessionalExperience " +
			  " WHERE AD_Client_ID = " + Env.getAD_Client_ID(m_ctx)+" AND AD_Org_ID = " + Env.getAD_Org_ID(m_ctx) + 
			  " AND StartDate = (SELECT MAX(StartDate) FROM HRM_ProfessionalExperience WHERE C_BPartner_ID = " + p_C_BPartner_ID + ")";
		
		String HRM_MainSpeciality = " ";
		String StartDate = " ";
		String HRM_Position = "";
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy");
		try
		{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				HRM_MainSpeciality = rs.getString("HRM_MainSpeciality");
				StartDate = dateFormat.format(rs.getDate("StartDate"));
				HRM_Position = rs.getString("HRM_Position");
			}
			
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		sheet.getRow(20).getCell(6).setCellValue(HRM_MainSpeciality);
		sheet.getRow(21).getCell(6).setCellValue(HRM_Position); 
		sheet.getRow(23).getCell(1).setCellValue(StartDate);
		sheet.getRow(25).getCell(6).setCellValue(StartDate);
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
					Util.launchFile(new File(file));
				//
		
		return Msg.translate(m_ctx, "Success");
	}

}
